<?php

// CentralDatabase.php
// Michael Baker
// 2010.06.21
//
// provides functionality for interaction with A3S' central database

if ( !defined( "IN_MDM" ) )
  exit;
  
require_once( 'Database.php' );
require_once( 'NetworkObject.php' );

class CentralDatabase extends Database
{

  ////////////////////////////////////////
  // ABSTRACT OBJECT MANAGEMENT
  ////////////////////////////////////////
  
  public function list_workflows()
  {
    // retrieve an array of (id, title) pairs
    
    $retval = array();
    $query  = "SELECT `id` FROM Workflow";
    $this->execute( $query );
    $resultset = $this->fetchall();
    
    foreach ( $resultset as $row )
    {
      $wf_id = $row[ 0 ];
      $wf = $this->get_workflow( $wf_id );
      $title = $wf->get_title();
      $retval[ $wf_id ] = $title;
    }
    
    // Sort it by the titles, ascending
    asort( $retval );
    return $retval;
  }
  
  public function get_workflow( $wf_id ) 
  {
    // retrieve a workflow object by its ID
    
    $this->escape( $wf_id );
    
    if ( !$this->workflow_exists( $wf_id ) )
      throw new DatabaseException( "Failed to get workflow $wf_id: Workflow does not exist" );
    
    // Retrieve corresponding nodecouple info from db
    $query  = "SELECT `couple_id`, `flip` FROM WorkflowPayload WHERE ";
    $query .= "`wf_id`='$wf_id' ORDER BY `order_id` ASC";
    $this->execute( $query );
    $resultset = $this->fetchall();
    
    $couples = array();
    
    // Generate NC objects from resultset, store in array
    foreach ( $resultset as $row )
    {
      $nc_id     = $row[ 0 ];
      $flip      = $row[ 1 ]; 
      $nc        = $this->get_nodecouple( $nc_id, $flip );
      $couples[] = $nc;
    }
    
    $wf = new Workflow( $couples );
    $wf->set_id( $wf_id );
    return $wf;
  }
  
  public function get_workflows()
  {
    // retrieve an array of all workflows
    
    // $flows1 maps: workflow ID -> ((nc_id, flip), ...)
    $flows1 = array();
    
    $query  = "SELECT `wf_id`, `couple_id`, `flip` FROM ";
    $query .= "WorkflowPayload ORDER BY `order_id` ASC";
    $this->execute( $query );
    $resultset = $this->fetchall();
    
    if ( !$resultset )
      return $flows1;
    
    foreach ( $resultset as $row )
    {
      $wf_id = $row[ 0 ];
      $nc_id = $row[ 1 ];
      $flip  = $row[ 2 ];
      
      if ( !array_key_exists( $wf_id, $flows1 ) )
        $flows1[ $wf_id ] = array();
      
      $flows1[ $wf_id ][] = array( $nc_id, $flip );
    }
    
    // $couples maps: couple ID -> (startID, endID)
    $couples    = array();
    
    $query  = "SELECT `id`, `initial_id`, `final_id` FROM ";
    $query .= "NodeCouple";
    $this->execute( $query );
    $resultset = $this->fetchall();
    
    foreach ( $resultset as $row )
    {
      $nc_id    = $row[ 0 ];
      $start_id = $row[ 1 ];
      $end_id   = $row[ 2 ];
      
      $couples[ $nc_id ] = array( $start_id, $end_id );
    }
    
    // $flows: ((flowID, (nID, nID, ...)), ...)
    $flows = array();
    
    // go through each workflow
    foreach ( array_keys( $flows1 ) as $wf_id )
    {
      $flow_nodes = array();
      $nc_couples = $flows1[ $wf_id ];
      
      foreach ( $nc_couples as $pair )
      {
        $nc_id    = $pair[ 0 ];
        $flip     = $pair[ 1 ];
        $id_array = $couples[ $couple_id ];
        
        // Flip the order of the nodecouple, if needed
        if ( $flip )
          $id_array = array_reverse( $id_array );
        
        array_push( $flow_nodes, $id_array[0], $id_array[1] );
      }
      
      // Update the flow array with the nodes
      $flows[ $wf_id ] = $flow_nodes;
    }
    
    return $flows;
  }
  
  public function workflow_exists( $wf_id )
  {
    // Check for a workflow ID in the Workflow table
    
    $this->escape( $wf_id );
    
    $query  = "SELECT `id` FROM Workflow WHERE `id`='$wf_id'";
    $this->execute( $query );
    return $this->numrows() ? true : false;
  }
  
  public function workflow_exists_by_nodes( $n_id_arr )
  {
    // Determine whether a workflow corresponding to an array of node IDs exists
    
    $reversed = array_reverse($n_id_arr);
    $workflows = $this->get_workflows();
    return ( array_search($n_id_arr, $workflows) || array_search($reversed, $workflows) );
  }

  public function workflow_exists_by_addr( $n_ip_arr )
  {
    $n_id_arr = array();
    
    foreach ( $n_ip_arr as $ip )
    {
      try
      {
        $node = $this->get_node_by_addr($ip);
        $n_id = $node->get_id();
      }
      catch (Exception $e)
      {
        // the workflow clearly can't exist since one of its IPs isn't in the DB.
        return false;
      }
      
      $n_id_arr[] = $n_id;
    }
    
    // now that we have an array of node IDs, dispatch to the previous function
    return $this->workflow_exists_by_nodes( $n_id_arr );
  }
  
  public function add_workflow()
  {
    // add a workflow to the Workflow table
    
    $query = "INSERT INTO Workflow () VALUES ()";
    $this->execute( $query );
    return $this->insert_id();
  }
  
  public function add_to_workflow( $wf_id, $nc_info )
  {
    // add a new couple to a workflow (WorkflowPayload table)
    
    $this->escape( $wf_id );
    
    $n = 0;
    if ( !( $this->workflow_exists( $wf_id ) ) )
      throw new DatabaseException( "Failed to add node to workflow $wf_id: Workflow does not exist" );
      
    // get the highest order # in the DB
    $query  = "SELECT `order_id` FROM WorkflowPayload WHERE ";
    $query .= "`wf_id`='$wf_id' ORDER BY order_id DESC LIMIT 1";
    $this->execute( $query );
    if ( $this->numrows() )
    {
      $row = $this->fetchone();
      $n = $row[ 0 ];
    }
    
    $order_id  = $n + 1;
    $nc_id = $nc_info[ 0 ];
    $flip  = $nc_info[ 1 ];
    $query  = "INSERT INTO WorkflowPayload ( `wf_id`, `couple_id`, ";
    $query .= "`flip`, `order_id` ) VALUES ( '$wf_id', '$nc_id', ";
    $query .= "'$flip', '$order_id' )";
    $this->execute( $query );
    return $this->insert_id();
  }
  
  public function search_for_workflows( $nc_id ) 
  {
    // search for workflows containing a certain NC ID
    
    $this->escape( $nc_id );
    
    $retval = array();
    $query = "SELECT `wf_id` FROM WorkflowPayload WHERE ";
    $query .= "couple_id='$nc_id'";
    $this->execute( $query );
    $resultset = $this->fetchall();
    foreach ( $resultset as $arr )
      $retval[] = $arr[ 0 ];
    
    return $retval;
  }

  public function create_workflow( $nc_arr )
  {
    // create a new workflow in the db
    // $nc_arr: array of IPs with repetitions {a, b, b, c, c, d, d, e}
    
    // check that there exists at least one threshing category
    if ( !count( $this->get_thresholding_categories() ) )
      throw new DatabaseException( "Failed to create workflow: No thresholds exist" );
      
    // check for even # of IPs
    if ( count($nc_arr) % 2 )
      throw new DatabaseException( "Failed to create workflow: IP list had uneven cardinality" );  
      
    // Arrays of new IDs that were the result of implicit creation
    $created_n   = array();
    $created_nc  = array();
    
    // Workflow sequence of (coupleObj, flip) arrays
    $wf_objects = array();
    
    // Workflow sequence of (coupleID, flip) arrays
    $wf = array();
    
    for ( $i = 0; $i < count($nc_arr); $i += 2 )
    {
      if ( ($i !== 0) && ($nc_arr[ $i ] !== $nc_arr[ $i - 1 ]) )
        throw new DatabaseException( "Failed to create workflow: Discontinuity detected" );
      
      $srcIP  = $nc_arr[ $i ];
      $targIP = $nc_arr[ $i + 1 ];
      
      $nc_arr = $this->spawn_nodecouple( $srcIP, $targIP );
      $wf_objects[] = $nc_arr;
    }
    
    foreach ( $wf_objects as $pair )
    {
      $nc = $pair[ 0 ];
      $flip = $pair[ 1 ];
      $nc_id = $nc->get_id();
      
      if ( $nc_id !== INVALID_ID )
      {
        $wf[] = array( $nc_id, $flip );
        continue;
      }
      
      // If we are still in the loop at this point, the NC must be created
      $nodes = $nc->get_nodes();
      $node_ids = array();
      
      foreach ( $nodes as $node )
      {
        $id = $node->get_id();
        if ( $id == INVALID_ID )
        {
          $nodeTitle = array( $node->get_title(0), $node->get_title(1) );
          $id = $this->add_node( $node->get_address(), $node->get_has_db() );
          $created_n[] = $id;
          $this->update_node_title( $id, $nodeTitle );
        }
        
        $node_ids[] = $id;
      }
      
      $nc_id = $this->add_nodecouple( $node_ids[0], $node_ids[1] );
      $created_nc[] = $nc_id;
      $this->update_nodecouple_db_flag( $nc_id, $nc->get_db_flag() );
      $wf[] = array( $nc_id, $flip );
    }
    
    $wf_id = $this->add_workflow();
    foreach ( $wf as $obj )
      $this->add_to_workflow( $wf_id, $obj );
    
    return array( $wf_id, $created_n, $created_nc );
  }
  
  public function drop_workflow( $workflow_id ) { 
    // drop a workflow from the database without any checking
    
    $this->escape( $workflow_id );
    
    $query = "DELETE FROM Workflow WHERE `id`='$workflow_id'";
    $this->execute( $query );  
    $query = "DELETE FROM WorkflowPayload WHERE `wf_id`='$workflow_id'";
    $this->execute( $query );
  }
  
  public function delete_workflow( $workflow_id ) { $this->drop_workflow( $workflow_id ); }
  
  
  
  public function get_node( $n_id )
  {
    // retrieve a node from the database and return it as a Node object
    
    $this->escape( $n_id );
    
    $query  = "SELECT `addr`, `has_db`, `title_s`, `title_l` ";
    $query .= "FROM Node WHERE `id`='$n_id'";
    $this->execute( $query );
    
    if ( !$this->numrows() )
      throw new DatabaseException( "Failed to get node $n_id: Node does not exist" );
    
    $resultset = $this->fetchall();
    $row = $resultset[ 0 ];
    $n_addr = $row[ 0 ];
    $n_hdb = $row[ 1 ];
    $n_stitle = $row[ 2 ];
    $n_ltitle = $row[ 3 ];
    $n = new Node( $n_addr, $n_hdb );
    $n->set_id( $n_id );
    $n->set_title( array( $n_stitle, $n_ltitle ) );
    return $n;
  }
  
  public function get_node_by_addr( $n_addr )
  {
    // retrieve a node from the database by its address, return Node object
    
    $this->escape( $n_addr );
    
    $query  = "SELECT `id` FROM Node WHERE `addr`='$n_addr'";
    $this->execute( $query );
    
    if ( !$this->numrows() )
      throw new DatabaseException( "Failed to get node $n_addr: Node does not exist" );
    
    $resultset = $this->fetchall();
    $row = $resultset[ 0 ];
    $n_id = $row[ 0 ];
    return $this->get_node( $n_id );
  }

  public function list_nodes()
  {
    // get a map of nodes (node ID) => node obj
    // sorted by ID ascending
    
    $query  = "SELECT `id`, `has_db`, `addr`, `title_s`, `title_l` FROM Node ";
    $query .= "ORDER BY `id` ASC";
    $this->execute( $query );
    $numrows = $this->numrows();
    
    $arr = array();;

    for ( $i = 0; $i < $numrows; $i++ )
    {
      $row = $this->fetchone();
      
      $n_id     = $row[ 0 ];
      $n_hdb    = $row[ 1 ];
      $n_addr   = $row[ 2 ];
      $n_stitle = $row[ 3 ];
      $n_ltitle = $row[ 4 ];
      $n = new Node( $n_addr, $n_hdb );
      $n->set_id( $n_id );
      $n->set_title( array( $n_stitle, $n_ltitle ) );
      
      $arr[ $n_id ] = $n;
    }
    
    return $arr;
  }
  
  public function update_node_title( $n_id, $n_title )
  {
    // update node title in database
    
    $stitle = $n_title[ 0 ];
    $ltitle = $n_title[ 1 ];
  
    $this->escape( $stitle );
    $this->escape( $ltitle );
    $this->escape( $n_id );
    
    $query  = "UPDATE Node SET `title_s`='$stitle', `title_l`='$ltitle' ";
    $query .= "WHERE `id`='$n_id'";
    $this->execute( $query );
  }
  
  public function add_node( $n_addr, $n_hdb = -1 )
  {
    // add a new node to the database
    // assume a node of this IP does not yet exist
    
    $this->escape( $n_addr );
    $this->escape( $n_hdb );
    
    if ( $node_hdb == HAS_DB_AUTODETECT )
      $n_hdb = device_has_db( $n_addr );
    
    $query  = "INSERT INTO Node ( `addr`, `has_db` ) ";
    $query .= "VALUES ( '$n_addr', '$n_hdb' )";
    $this->execute( $query );
    return $this->insert_id();
  }
  
  public function drop_node( $n_id )
  {
    // drop a node from the database without any checking
    // (could compromise data integrity)
    
    $this->escape( $n_id );
    
    $query = "DELETE FROM Node WHERE `id`='$n_id'";
    $this->execute( $query );
  }
  
  public function delete_node( $n_id ) { $this->drop_node( $n_id ); }
  
  
  
  public function get_nodecouple( $nc_id, $flip = false )
  {
    // get a nodecouple by its ID
    
    $this->escape( $nc_id );
    
    $query  = "SELECT `initial_id`, `final_id`, `db_flag` FROM ";
    $query .= "NodeCouple WHERE `id`='$nc_id'";    
    $this->execute( $query );

    if ( !$this->numrows() )
      throw new DatabaseException( "Failed to get nodecouple $nc_id: Nodecouple does not exist" );
    
    $nodes = array();
    $row = $this->fetchone();
    $initial = $this->get_node( $row[ 0 ] );
    $final   = $this->get_node( $row[ 1 ] );
    $db_flag = $row[ 2 ];
    array_push( $nodes, $initial, $final );
    
    // Perform flip on nodes if necessary
    $initial = $nodes[ ($flip) ? 1 : 0 ];
    $final = $nodes[ ($flip) ? 0 : 1 ];
    
    // Retrieve its category ID in order to retrieve its title
    $cat_id = $this->get_thresholding_category( $nc_id );
    $cats = $this->get_thresholding_categories();
    if ( array_key_exists( $cat_id, $cats ) )
      $title = $cats[ $cat_id ];
    else
      $title = NC_TITLE_UNKNOWN;
    
    // Create the NC object
    $nc = new NodeCouple( $initial, $final );
    $nc->set_id( $nc_id );
    $nc->set_title( $title );
    
    // If we're flipping the node, invert the DB flag
    $nc->set_db_flag( ($flip) ? (!$db_flag) : ($db_flag) );
    
    return $nc;
  }
  
  public function spawn_nodecouple( $n_addr1, $n_addr2 )
  {
    // get an array( $nodecouple_obj, $flip ) that did not necessarily come from
    // the database -- to be used in workflow creation
    
    $srcIP = $n_addr1; $targIP = $n_addr2;
    /* First, we obtain objects for the source and target nodes */
    try
    {
      $srcObj = $this->get_node_by_addr( $srcIP );
      $src_is_new = false;
    }
    catch (DatabaseException $e)
    {
      $src_is_new = true;
      
      $responding = device_responds( $srcIP );
      $hdb = false;
      if ( $responding ) { $hdb = device_has_db( $srcIP ); }
      $srcObj = new Node( $srcIP, $hdb );
      $srcObj->set_id( INVALID_ID );
    }
    
    try
    {
      $targObj = $this->get_node_by_addr( $targIP );
      $targ_is_new = false;
    }
    catch (DatabaseException $e)
    {
      $targ_is_new = true;
      
      $responding = device_responds( $targIP );
      $hdb = false;
      if ( $responding ) { $hdb = device_has_db( $targIP ); }
      $targObj = new Node( $targIP, $hdb );
      $targObj->set_id( INVALID_ID );
    }
    
    /* Now resolve titles for them if they are new nodes */
    $srcTitle = array( $srcIP, $srcIP ); $targTitle = array( $targIP, $targIP );
    $objects = array( $srcObj, $targObj );
    foreach ( $objects as $obj )
    {
      if ( !( ( $cond1 = $obj->qdb_suffices( $srcIP ) ) || ( $cond2 = $obj->qdb_suffices( $targIP ) ) ) )
        continue;
      
      $qdb = $obj->get_qdb();
      $qdb->set_date( get_ymd() );
      if ( $src_is_new && $qdb->has_data( $srcIP ) )
        $srcTitle = $qdb->get_node_title( $srcIP );
      if ( $targ_is_new && $qdb->has_data( $targIP ) )
        $targTitle = $qdb->get_node_title( $targIP );
      $qdb->close();
    }
    
    if ( $src_is_new ) $srcObj->set_title( $srcTitle );
    if ( $targ_is_new ) $targObj->set_title( $targTitle );
    
    /* Now create (if necessary) the nodecouples */
    $srcID = $srcObj->get_id(); $targID = $targObj->get_id();
    
    if ( ( $srcID == INVALID_ID ) || ( $targID == INVALID_ID ) )
    {
      $nc = new NodeCouple( $srcObj, $targObj );
      $nc->set_id( INVALID_ID );
      $nc_flip = false;
      if ( $srcObj->qdb_suffices( $targIP ) ) $db_flag = 0;
      else if ( $targObj->qdb_suffices( $srcIP ) ) $db_flag = 1;
      else throw new DatabaseException( "Failed to spawn nodecouples: No data source on segment ($srcIP, $targIP)" );
      $nc->set_db_flag( $db_flag );
    }
    else
    {
      try
      {
        $nc_info = $this->get_nodecouple_by_node( $srcID, $targID );
        $nc = $this->get_nodecouple( $nc_info[ 0 ] );
        $nc_flip = $nc_info[ 1 ];
      }
      catch (DatabaseException $e)
      {
        $nc = new NodeCouple( $srcObj, $targObj );
        $nc->set_id( INVALID_ID );
        $nc_flip = false;
        if ( $srcObj->qdb_suffices( $targIP ) ) $db_flag = 0;
        else if ( $targObj->qdb_suffices( $srcIP ) ) $db_flag = 1;
        else throw new DatabaseException( "Failed to spawn nodecouples: No data source on segment ($srcIP, $targIP)" );
        $nc->set_db_flag( $db_flag );
      }
    }
    
    return array( $nc, $nc_flip );
  }
  
  public function get_nodecouple_by_node( $node_id1, $node_id2 )
  {
    // get a nodecouple info array by searching for the initial and final points
    
    $this->escape( $node_id1 );
    $this->escape( $node_id2 );
    
    $query  = "SELECT id, initial_id, final_id FROM NodeCouple ";
    $query .= "WHERE (initial_id='$node_id1' AND final_id='$node_id2') ";
    $query .= "OR (initial_id='$node_id2' AND final_id='$node_id1')";
    
    $this->execute( $query );
    
    if ( !$this->numrows() )
      throw new DatabaseException( "Failed to get nodecouple for endpoints $node_id1 and $node_id2: No couple found" );
    
    $row = $this->fetchone();
    
    $couple_id = $row[ 0 ]; 
    $initial   = $row[ 1 ];
    $final     = $row[ 2 ];
    
    if ( ($initial == $node_id1) && ($final == $node_id2) )
      $flip = false;
    elseif ( ($initial == $node_id2) && ($final == $node_id1) )
      $flip = true;
    else
      throw new DatabaseException( "Failed to get nodecouple for endpoints $node_id1 and $node_id2: Data inconsistency" );
    
    return array( $couple_id, $flip );
  }

  public function search_for_nodecouples( $node_id )
  {
    // search for a nodecouple originating or terminating at a given node
    
    $this->escape( $node_id );
    
    $retval = array();
    
    $query  = "SELECT id FROM NodeCouple ";
    $query .= "WHERE initial_id='$node_id' OR final_id='$node_id'";
    
    $this->execute( $query );
    $resultset = $this->fetchall();
    
    foreach ( $resultset as $arr ) {
      $retval[] = $arr[ 0 ];
    }
    
    return $retval;
  }
  
  public function get_nodecouples_by_category( $category_id )
  {
    $this->escape( $node_id );
    
    $retval = array();
    
    $query  = "SELECT id FROM NodeCouple WHERE category_id='$category_id'";
    
    $this->execute( $query );
    $resultset = $this->fetchall();
    
    foreach ( $resultset as $arr ) {
      $retval[] = $arr[ 0 ];
    }
    
    return $retval;
    
  }
  
  public function update_nodecouple_db_flag( $couple_id, $db_flag )
  {
    // updates the db_flag column of a certain nodecouple in the database
    
    // db_flag - a boolean value 0 (initial) or 1 (final)
    // determines whether the initial or final node of a segment
    // contains a database with data representing interactions between
    // both nodes on that segment.
    
    $this->escape( $couple_id );
    $this->escape( $db_flag );
    
    $query  = "UPDATE NodeCouple SET db_flag='$db_flag' ";
    $query .= "WHERE id='$couple_id'";
    
    $this->execute( $query );
  }
  
  public function add_nodecouple( $node_id1, $node_id2 )
  {
    // add a new nodecouple - this does NOT set the "db_flag" !
    
    $this->escape( $node_id1 );
    $this->escape( $node_id2 );
    
    $query  = "INSERT INTO NodeCouple ( `initial_id`, `final_id` ) ";
    $query .= "VALUES ( '$node_id1', '$node_id2' )";
    
    $this->execute( $query );
    
    $couple_id = $this->insert_id();
    
    // retrieve lowest thresholding category ID
    $cats = $this->get_thresholding_categories();
    ksort($cats);
    $cats = array_keys($cats);
    $cat_id = $cats[0];
    
    // associate it to that category ID
    $this->make_thresholding_assoc( $couple_id, $cat_id );
    
    return $couple_id;
  }
  
  public function drop_nodecouple( $couple_id )
  {
    // delete a nodecouple
    
    $this->escape( $couple_id );
    
    $query = "DELETE FROM NodeCouple WHERE id='$couple_id'";
    $this->execute( $query );
  }
  
  public function delete_nodecouple( $couple_id ) { $this->drop_nodecouple( $couple_id ); }
  
  
  ////////////////////////////////////////
  // THRESHOLDING, COLOR CODES, ETC.
  ////////////////////////////////////////
  
  public function get_thresholding_categories()
  {
    // retrieve an associative array of thresholding categories
    
    $query  = "SELECT id, title FROM ThresholdCategory ";
    $query .= "ORDER BY id ASC";
    $this->execute( $query );
    $resultset = $this->fetchall();
    
    $retval = array();
    
    foreach ( $resultset as $row ) {
      $id = $row[ 0 ];
      $title = $row[ 1 ];
      
      $retval[ $id ] = $title;
    }
    
    return $retval;
  }
  
  public function get_thresholding_mapcolor( $cat_id )
  {
    $this->escape( $cat_id );
    
    $query  = "SELECT mapcolor FROM ThresholdCategory WHERE ";
    $query .= "`id`='$cat_id'";
    $this->execute( $query );
    $resultset = $this->fetchall();
    
    if ( !count($resultset) )
      throw new DatabaseException( "Failed to retrieve mapcolor for category $cat_id" );
      
    return $resultset[0][0];
  }
  
  public function get_thresholding_category( $couple_id )
  {
    // get the thresholding category ID of a certain node couple
    
    $this->escape( $couple_id );
    
    $query  = "SELECT category_id FROM NodeCouple ";
    $query .= "WHERE id='$couple_id'";
    $this->execute( $query );
    
    if ( !$this->numrows() )
      throw new DatabaseException( "Failed to get nodecouple thresholding category: Nodecouple ID $couple_id does not exist" );
    
    $row = $this->fetchone();
    
    return $row[ 0 ];
  }
  
  public function update_thresholding_category( $cat_id, $title, $mapcolor )
  {
    // change a thresholding category
    
    $this->escape( $cat_id );
    $this->escape( $title );
    $this->escape( $mapcolor );
    
    $query  = "UPDATE ThresholdCategory SET `title`='$title', ";
    $query .= "`mapcolor`='$mapcolor' WHERE `id`='$cat_id'";
    $this->execute( $query );
  }
  
  public function add_thresholding_category( $title )
  {
    // add a new thresholding category
    
    $this->escape( $title );
    
    $query  = "INSERT INTO ThresholdCategory ";
    $query .= "( `title`, `mapcolor` ) VALUES ( '$title', '" . DEFAULT_MAPCOLOR . "' )";
    $this->execute( $query );
    $id = $this->insert_id();
    
    /*** EDIT: add threshold stuff ***/
    for ( $ctr = 0; $ctr < 2; $ctr++ )
    {
      $arr = array();
      for ( $k = 1; $k < 5; $k++ )
        $arr[] = array( '0', '0', '1', '1', '0', $k );
      
      $this->add_thresholds( $id, $ctr, $arr );
    }
    
    return $id;
  }
  
  public function delete_thresholding_category( $category_id )
  {
    return $this->drop_thresholding_category( $category_id );
  }
  
  public function drop_thresholding_category( $category_id )
  {
    // drop a thresholding category from ThresholdCategory table
    
    $this->escape( $category_id );
    $query  = "DELETE FROM ThresholdCategory WHERE id='$category_id'";
    $this->execute( $query );
  }
  
  public function make_thresholding_assoc( $couple_id, $category_id )
  {
    // update the NodeCouple index to make an association to a certain category
    // assumes that the couple exists
        
    $this->escape( $couple_id );
    $this->escape( $category_id );
    
    $query  = "UPDATE NodeCouple SET category_id='$category_id' WHERE ";
    $query .= "id='$couple_id'"; 
    $this->execute( $query );
  }
  
  public function add_thresholds( $category_id, $fault_id, $thresh_arr )
  {
    // add thresholding brackets for a certain category ID and fault ID
    // $thresh_arr: an array of 6-element arrays
    // layout of individual array: lower bound, upper bound, lower hardness boolean, upper hardness boolean, ubound_ignore, colorcode
    
    // color codes must be added first with $this->add_colorcode() !
    
    $this->escape( $category_id );
    $this->escape( $fault_id );
    
    foreach ( $thresh_arr as $bracket )
    {
      for ( $i = 0; $i < count($bracket); $i++ )
      {
        $item = $bracket[ $i ];
        $this->escape($item);
        $bracket[ $i ] = $item;
      }
        
      $lbound = $bracket[ 0 ];
      $ubound = $bracket[ 1 ];
      $lhard  = $bracket[ 2 ];
      $uhard  = $bracket[ 3 ];
      $ubound_ignore = $bracket[ 4 ];
      $color  = $bracket[ 5 ];
      
      $row_id = 0;
      $query  = "SELECT `id` FROM Threshold WHERE `category_id`='$category_id' ";
      $query .= "AND `fault_id`='$fault_id' AND `color_code`='$color'";
      $this->execute( $query );
      $resultset = $this->fetchall();
      if ( count($resultset) )
        $row_id = $resultset[ 0 ][ 0 ];
      
      $iquery  = "INSERT INTO Threshold ( `category_id`, `fault_id`, ";
      $iquery .= "`floor`, `ceiling`, `floor_hard`, `ceiling_hard`, ";
      $iquery .= "`ignore_ceiling`, `color_code` ) VALUES ";
      $iquery .= "( '$category_id', '$fault_id', '$lbound', '$ubound', ";
      $iquery .= "'$lhard', '$uhard', '$ubound_ignore', '$color' )";
      
      $uquery  = "UPDATE Threshold SET `floor`='$lbound', `ceiling`='$ubound', ";
      $uquery .= "`floor_hard`='$lhard', `ceiling_hard`='$uhard', ";
      $uquery .= "`ignore_ceiling`='$ubound_ignore' WHERE `id`='$row_id'";
      
      $q = ( $row_id ? $uquery : $iquery );
      
      $this->execute( $q );
    }
    
  }
  
  public function perform_thresholding( $category_id, $fault_id, $numeric )
  {
    // retrieve threshold color for a certain numeric
    
    $this->escape( $category_id );
    $this->escape( $fault_id );
    
    $query  = "SELECT floor, ceiling, floor_hard, ";
    $query .= "ceiling_hard, color_code, ignore_ceiling FROM ";
    $query .= "Threshold WHERE category_id=";
    $query .= "'$category_id' AND fault_id=";
    $query .= "'$fault_id' ORDER BY floor ASC";
    
    $this->execute( $query );
    $resultset = $this->fetchall();
    
    if ( !$resultset )
    {
      // default color code (normally gray)
      error_log( "No threshold values found for category $category_id, fault $fault_id" );
      return COLOR_NOTHRESH;
    }
    
    foreach ( $resultset as $row )
    {
      $floor  = $row[ 0 ];
      $ceil   = $row[ 1 ];
      $floorh = $row[ 2 ];
      $ceilh  = $row[ 3 ];
      $color  = $row[ 4 ];
      $ig     = $row[ 5 ];

      $cond1 = $floorh ? ( $floor < $numeric ) : ( $floor <= $numeric );
      $cond2 = $ceilh  ? ( $ceil  > $numeric ) : ( $ceil  >= $numeric );
      $cond  = $ig     ? ( $cond1 )            : ( $cond1 && $cond2 );
      
      if ( $cond )
        return $color;
    }
    
    error_log( "Numeric $numeric failed to colormatch (fault id $fault_id, category $category_id)." );
    return COLOR_NOMATCH;
  }
  
  public function get_thresholds( $category_id, $fault_id )
  {
    // retrieve thresholding values for a certain category + fault
    
    $this->escape( $category_id );
    $this->escape( $fault_id );
    
    $query  = "SELECT `floor`, `ceiling`, `floor_hard`, ";
    $query .= "`ceiling_hard`, `ignore_ceiling`, ";
    $query .= "`color_code` FROM Threshold WHERE ";
    $query .= "`category_id`='$category_id' AND ";
    $query .= "`fault_id`='$fault_id' ORDER BY `color_code` ASC";
    $this->execute( $query );
    $resultset = $this->fetchall();
    
    return $resultset;
  }
  
  public function list_colorcodes()
  {
    // list colorcodes
    
    $retval = array();
    
    $query  = "SELECT code, red, green, blue FROM ";
    $query .= "ColorCode ORDER BY code ASC";
    
    $this->execute( $query );
    
    for ( $ctr = 0; $ctr < $this->numrows(); $ctr++ )
    {
      $row = $this->fetchone();
      $code = $row[ 0 ];
      $tuple = array_slice( $row, 1 );
      
      $retval[ $code ] = $tuple;
    }
    
    return $retval;
  }
  
  public function get_colorcode( $code )
  {
    // retrieve the RGB tuple of a color code from the database
    
    $this->escape( $code );
    
    $query  = "SELECT red, green, blue FROM ";
    $query .= "ColorCode WHERE code='$code'";
    
    $this->execute( $query );
    
    if ( !$this->numrows() )
      throw new DatabaseException( "Failed to retrieve colorcode $code" );

    $row = $this->fetchone();
    return $row;
  }
  
  public function edit_colorcode( $code, $red, $green, $blue )
  {
    // update the RGB tuple of a color code from the database
    
    $this->escape( $code );
    $this->escape( $red ); $this->escape( $green ); $this->escape( $blue );
    
    $query  = "UPDATE ColorCode SET red='$red', green='$green', blue='$blue' ";
    $query .= "WHERE code='$code'";
    
    $this->execute( $query );
  }
  
  public function add_colorcode( $code, $red, $green, $blue )
  {
    // add a new color code to the database
    
    $this->escape( $code );
    $this->escape( $red ); $this->escape( $green ); $this->escape( $blue );
    
    $query  = "INSERT INTO ColorCode ( `code`, `red`, `green`, `blue` ) ";
    $query .= "VALUES ( '$code', '$red', '$green', '$blue' )";
    
    $this->execute( $query ); 
    return $this->insert_id();
  }
  
  public function drop_colorcode( $code )
  {
    // drop a color code from the database 
    
    $this->escape( $code );
    
    $query  = "DELETE FROM ColorCode WHERE code='$code'"; 
    $this->execute( $query );
  }
  
  public function delete_colorcode( $code )
  {
    return $this->drop_colorcode( $code );
  }
}

?>